# -!- coding: utf-8 -!-
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
#作者：cacho_37967865
#博客：https://blog.csdn.net/sinat_37967865
#文件：linuxTop.py
#日期：2019-05-20
#备注：发起性能测试前，top -b -d 1 > /data/top500.txt将性能信息重定向文件,然后发起性能测试;最后将文件的内容整理后存入到数据库。
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

import re
import pymysql


db = pymysql.connect(
    host="localhost",
    user="root",
    password="123456",
    port=3306,
    use_unicode=True,
    charset="utf8",
    database="cacho")

cursor = db.cursor()

def createTable():
    sql1 = 'drop table if exists linux_top;'
    sql2 = 'create table linux_top(top_time VARCHAR(32),binfa_num int,average1 float,average5 float,average15 float,us_cpu VARCHAR(15),' \
           'sy_cpu VARCHAR(15),wa_cpu VARCHAR(15),ni_cpu VARCHAR(15),id_cpu VARCHAR(15),hi_cpu VARCHAR(15),si_cpu VARCHAR(15),' \
           'st_cpu VARCHAR(15),men_total VARCHAR(25),men_used VARCHAR(20),men_free VARCHAR(20),men_buffers VARCHAR(20),PRIMARY KEY (top_time));'
    cursor.execute(sql1)
    cursor.execute(sql2)
    db.commit()


def insert_data(top_time,binfa_num,average1,average5,average15,us_cpu,sy_cpu,wa_cpu,ni_cpu,id_cpu,hi_cpu,si_cpu,st_cpu,men_total,men_used,men_free,men_buffers):
    sql = "insert into linux_top(top_time,binfa_num,average1,average5,average15,us_cpu,sy_cpu,wa_cpu,ni_cpu,id_cpu,hi_cpu,si_cpu,st_cpu,men_total,men_used,men_free,men_buffers) " \
          "values ('%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s','%s');" \
              % (top_time,binfa_num,average1,average5,average15,us_cpu,sy_cpu,wa_cpu,ni_cpu,id_cpu,hi_cpu,si_cpu,st_cpu,men_total,men_used,men_free,men_buffers)
    try:
        cursor.execute(sql)
    except Exception as e:
        # 发生错误时回滚
        db.rollback()
        print(str(e))
    else:
        db.commit()  # 事务提交


def get_auditTime(file,date):
    logs = open(file, 'r' ,encoding= 'utf8')
    load = []
    try:
        for line in logs.readlines():
            line = line.rstrip()
            if line[0:3] == 'top':
                top_time = date + line[6:14]
                #load_average = line[-16:]
                average1 = line[-16:-12]
                average5 = line[-10:-6]
                average15 = line[-4:]
                print(average1,average5,average15)
                row = []
                row.append(top_time)
                row.append(average1)
                row.append(average5)
                row.append(average15)


            elif line[0:6] == 'Cpu(s)':
                line = line.replace('Cpu(s):  ','')
                us_cpu = re.findall(r'(\d.*)us',line)[0]
                sy_cpu = re.findall(r'us, *(\d.*)sy',line)[0]
                wa_cpu = re.findall(r'id, *(\d.*)wa', line)[0]
                ni_cpu = re.findall(r'sy, *(\d.*)ni',line)[0]
                id_cpu = re.findall(r'ni, *(\d.*)id', line)[0]
                hi_cpu = re.findall(r'wa, *(\d.*)hi', line)[0]
                si_cpu = re.findall(r'hi, *(\d.*)si', line)[0]
                st_cpu = re.findall(r'si, *(\d.*)st', line)[0]
                row.append(us_cpu)
                row.append(sy_cpu)
                row.append(wa_cpu)
                row.append(ni_cpu)
                row.append(id_cpu)
                row.append(hi_cpu)
                row.append(si_cpu)
                row.append(st_cpu)
                #print(line)
                #print(us_cpu,'-->'+sy_cpu,'-->'+ni_cpu,'-->'+id_cpu,'-->'+wa_cpu,'-->'+hi_cpu,'-->'+si_cpu,'-->'+st_cpu)
            elif line[0:4] == 'Mem:':
                #print(line)
                men_total = re.findall(r' *(\d.*)total',line)[0]
                men_used = re.findall(r'total, *(\d.*)used', line)[0]
                men_free = re.findall(r'used, *(\d.*)free', line)[0]
                men_buffers = re.findall(r'free, *(\d.*)buffers', line)[0]
                row.append(men_total)
                row.append(men_used)
                row.append(men_free)
                row.append(men_buffers)
                #print(men_total, men_used, men_free, men_buffers)
                #print(row)
                load.append(row)
    finally:
        logs.close()
    return load


if __name__ == '__main__':
    createTable()
    top_info = get_auditTime('E:\zenglingwei\\top.txt','2019-08-13 ')
    for info in top_info:
        insert_data(info[0], 3385, float(info[1]), float(info[2]), float(info[3]), info[4], info[5], info[6], info[7], info[8],
                    info[9],info[10], info[11], info[12], info[13],info[14],info[15])